
USE PEDIDOS
--
ALTER DATABASE PEDIDOS
ADD FILEGROUP ANTES_2005;

ALTER DATABASE PEDIDOS
ADD FILE
   ( NAME = 'PEDIDOS_VAZIO_ANTES_2005',
     FILENAME = 'C:\DADOS\PEDIDOS_VAZIO_ANTES_2005.NDF',
     SIZE = 5, MAXSIZE = 10, FILEGROWTH = 1 ) 
TO FILEGROUP ANTES_2005;

ALTER DATABASE PEDIDOS
ADD FILEGROUP S1_2005


ALTER DATABASE PEDIDOS
ADD FILE
   ( NAME = 'PEDIDOS_VAZIO_S1_2005',
     FILENAME = 'C:\DADOS\PEDIDOS_VAZIO_S1_2005.NDF',
     SIZE = 5, MAXSIZE = 10, FILEGROWTH = 1 )
TO FILEGROUP S1_2005

ALTER DATABASE PEDIDOS
ADD FILEGROUP S2_2005

ALTER DATABASE PEDIDOS
ADD FILE
   ( NAME = 'PEDIDOS_VAZIO_S2_2005',
     FILENAME = 'C:\DADOS\PEDIDOS_VAZIO_S2_2005.NDF',
     SIZE = 5, MAXSIZE = 10, FILEGROWTH = 1 )
TO FILEGROUP S2_2005

ALTER DATABASE PEDIDOS
ADD FILEGROUP S1_2006

ALTER DATABASE PEDIDOS
ADD FILE
   ( NAME = 'PEDIDOS_VAZIO_S1_2006',
     FILENAME = 'C:\DADOS\PEDIDOS_VAZIO_S1_2006.NDF',
     SIZE = 5, MAXSIZE = 10, FILEGROWTH = 1 )
TO FILEGROUP S1_2006

ALTER DATABASE PEDIDOS
ADD FILEGROUP S2_2006

ALTER DATABASE PEDIDOS
ADD FILE
   ( NAME = 'PEDIDOS_VAZIO_S2_2006',
     FILENAME = 'C:\DADOS\PEDIDOS_VAZIO_S2_2006.NDF',
     SIZE = 5, MAXSIZE = 10, FILEGROWTH = 1 )
TO FILEGROUP S2_2006

ALTER DATABASE PEDIDOS
ADD FILEGROUP S1_2007

ALTER DATABASE PEDIDOS
ADD FILE
   ( NAME = 'PEDIDOS_VAZIO_S1_2007',
     FILENAME = 'C:\DADOS\PEDIDOS_VAZIO_S1_2007.NDF',
     SIZE = 5, MAXSIZE = 10, FILEGROWTH = 1 )
TO FILEGROUP S1_2007

ALTER DATABASE PEDIDOS
ADD FILEGROUP S2_2007

ALTER DATABASE PEDIDOS
ADD FILE
   ( NAME = 'PEDIDOS_VAZIO_S2_2007',
     FILENAME = 'C:\DADOS\PEDIDOS_VAZIO_S2_2007.NDF',
     SIZE = 5, MAXSIZE = 10, FILEGROWTH = 1 )
TO FILEGROUP S2_2007

CREATE PARTITION FUNCTION SemestresPFN( datetime )
AS
RANGE RIGHT FOR VALUES (
                         '2005.1.1',  -- Anteriores a 2005.1.1      -> ANTES_2005
                         '2005.7.1',  -- >= 2005.1.1 AND < 2005.7.1 -> S1_2005
                         '2006.1.1',  -- >= 2005.7.1 AND < 2006.1.1 -> S2_2005
                         '2006.7.1',  -- >= 2006.1.1 AND < 2006.7.1 -> S1_2006
                         '2007.1.1',  -- >= 2006.7.1 AND < 2007.1.1 -> S2_2006
                         '2007.7.1'   -- >= 2007.1.1 AND < 2007.7.1 -> S1_2007
                                      -- >= 2007.7.1                -> S2_2007
                        )
GO

CREATE PARTITION SCHEME SemestresScheme
AS
PARTITION SemestresPFN TO
(ANTES_2005, S1_2005, S2_2005, S1_2006, S2_2006, S1_2007, S2_2007 )
GO


ALTER TABLE PEDIDOS DROP PK_PEDIDOS

ALTER TABLE PEDIDOS ALTER COLUMN DATA_EMISSAO DATETIME NOT NULL

ALTER TABLE PEDIDOS ADD CONSTRAINT
 PK_PEDIDOS PRIMARY KEY (NUM_PEDIDO, DATA_EMISSAO) 
ON SemestresScheme(DATA_emissao)

SELECT T.NAME AS NOME_TABELA, 
       I.NAME AS NOME_PK, 
       PS.NAME AS NOME_P_SCHEMA,
       PF.NAME AS NOME_P_FUNCTION , 
       FG.NAME AS NOME_FILEGROUP, 
       PR.ROWS AS LINHAS_GRAVADAS
FROM  SYS.TABLES T 
      JOIN SYS.INDEXES I ON T.OBJECT_ID = I.OBJECT_ID
      JOIN SYS.PARTITION_SCHEMES PS ON I.DATA_SPACE_ID = PS.DATA_SPACE_ID
      JOIN SYS.DESTINATION_DATA_SPACES DS ON PS.DATA_SPACE_ID = DS.PARTITION_SCHEME_ID
      JOIN SYS.FILEGROUPS FG ON DS.DATA_SPACE_ID = FG.DATA_SPACE_ID
      JOIN SYS.PARTITIONS PR ON DS.DESTINATION_ID = PR.PARTITION_NUMBER AND PR.OBJECT_ID = T.OBJECT_ID
      JOIN SYS.PARTITION_FUNCTIONS PF ON PS.FUNCTION_ID = PF.FUNCTION_ID


